package com.jsmtr.rms.utils;

import cn.ktjk.DataTableUtile.DataTable.DataRow;
import cn.ktjk.DataTableUtile.DataTable.DataTable;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberComparer;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

/**
 *
 * @author: cyx
 * @description:
 * @date: Created in 2018/8/3 16:15
 * @modified by:
 */
public class ExcelHelper {

    private final static Logger LOG = LoggerFactory.getLogger(ExcelHelper.class);

    //region 读取Excel文件返回DataTable
    /**    
     * 读取Excel文件返回DataTable
     * @author cyx  
     * @date 2018/8/3 16:24
     * @param file  
     * @return cn.ktjk.DataTableUtile.DataTable.DataTable  
     */ 
    public static DataTable ReadExcelToDataTable(MultipartFile file) throws Exception {
        DataTable dataTable;
        Workbook workbook;
        String exname = StringTools.getFileExtension(file.getOriginalFilename());//扩展名（.xxx）
        String fileName = file.getOriginalFilename();
        if (!exname.equalsIgnoreCase(".xls") && !exname.equalsIgnoreCase(".xlsx")) {
          throw new Exception("只能上传后缀为：xls和xlsx的Excel文件") ;
        }

        if (exname.equalsIgnoreCase(".xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());
        }

        Sheet sheet = workbook.getSheetAt(0); //得到第一个工作表
        String sheetname = sheet.getSheetName();
        dataTable = new DataTable(sheetname);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        Row titleRow = sheet.getRow(firstRowNum);
        DataRow dataRow;
        int lastCellNum=0;
        int firstCellNum=0;
        if (titleRow != null) {
            lastCellNum=titleRow.getLastCellNum();
            firstCellNum=titleRow.getFirstCellNum();
            for (int i=firstCellNum;i<lastCellNum;i++){
                Cell cell = titleRow.getCell(i);
                String value=cell.getStringCellValue();
                if (StringTools.notEmpty(value)){
                    dataTable.Columns.Add(value.trim());
                }

            }
            dataTable.Columns.Add("导入状态");
        }

        for (int i = firstRowNum + 1; i <=lastRowNum; i++) {
            Row excelRow = sheet.getRow(i);
            if (excelRow != null) {
                dataRow = dataTable.NewRow();
                for (int j = 0; j < dataTable.Columns.size()-1; j++) {
                    if(excelRow.getCell(j)!=null){
                       /* try {
                            String value=excelRow.getCell(j).getStringCellValue();
                            if (StringTools.notEmpty(value)){
                                dataRow.setValue(j,value.trim());
                            }
                        }catch (Exception e){
                            dataRow.setValue(j, excelRow.getCell(j).getNumericCellValue());
                        }*/
                       dataRow.setValue(j,getCellValue(excelRow.getCell(j)));
                    }else{
                        dataRow.setValue(j,"");
                    }
                }
                dataRow.setValue(dataTable.Columns.size()-1,"未导入");
                dataTable.Rows.add(dataRow);
            }
        }

        workbook.close();
        return dataTable;
    }
    //endregion

    public static DataTable ReadMergeExcelToDataTable(MultipartFile file,Integer sheetIndex,Integer endIndex) throws Exception {
        DataTable dataTable;
        Workbook workbook;
        String exname = StringTools.getFileExtension(file.getOriginalFilename());//扩展名（.xxx）
        String fileName = file.getOriginalFilename();
        if (!exname.equalsIgnoreCase(".xls") && !exname.equalsIgnoreCase(".xlsx")) {
            throw new Exception("只能上传后缀为：xls和xlsx的Excel文件") ;
        }

        if (exname.equalsIgnoreCase(".xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());
        }

        Sheet sheet = workbook.getSheetAt(sheetIndex); //得到第一个工作表
        String sheetname = sheet.getSheetName();
        dataTable = new DataTable(sheetname);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        Row titleRow = sheet.getRow(endIndex);
        DataRow dataRow;
        int lastCellNum=0;
        int firstCellNum=0;
        if (titleRow != null) {
            lastCellNum=titleRow.getLastCellNum();
            firstCellNum=titleRow.getFirstCellNum();
            for (int i=firstCellNum;i<lastCellNum;i++){
                Cell cell = titleRow.getCell(i);
                if(isMergedRegion(sheet,cell.getRowIndex(),cell.getColumnIndex())){
                 String cellValue=   getMergedRegionValue(sheet,cell.getRowIndex(),cell.getColumnIndex());
                    dataTable.Columns.Add(cellValue);
                }else{
                    dataTable.Columns.Add(getCellValue(cell));
                }
                //dataTable.Columns.Add(cell.getStringCellValue());
            }
            dataTable.Columns.Add("导入状态");
        }

        for (int i = firstRowNum + 1; i <=lastRowNum; i++) {
            Row excelRow = sheet.getRow(i);
            if (excelRow != null) {
                dataRow = dataTable.NewRow();
                for (int j = 0; j < dataTable.Columns.size()-1; j++) {
                    if(excelRow.getCell(j)!=null){
                       /* try {
                            dataRow.setValue(j, excelRow.getCell(j).getStringCellValue());
                        }catch (Exception e){
                            dataRow.setValue(j, excelRow.getCell(j).getNumericCellValue());
                        }*/
                        dataRow.setValue(j,getCellValue(excelRow.getCell(j)));
                    }else{
                        dataRow.setValue(j,"");
                    }
                }
                dataRow.setValue(dataTable.Columns.size()-1,"未导入");
                dataTable.Rows.add(dataRow);
            }
        }

        workbook.close();
        return dataTable;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }


    /*
     * 根据DataTable数据创建新的WorkBook输出对象
     * @author cyx
     * @date 2018/8/3 15:37
     * @param dataTable
     * @return org.apache.poi.ss.usermodel.Workbook
     */
    public static Workbook DataTableToExcel(DataTable dataTable,String sheetName) throws Exception{
        Workbook workbook =new HSSFWorkbook();
        Sheet sheet= workbook.createSheet(sheetName);
        Row titlerow=sheet.createRow(0);
        int errorIndex=0;
        if(dataTable.Columns!=null&&dataTable.Columns.size()>0){
            for (int i = 0; i <dataTable.Columns.size() ; i++) {
                Cell title= titlerow.createCell(i, CellType.STRING);
                String colunmName=dataTable.Columns.get(i).ColumnName;
                if(i==(dataTable.Columns.size()-1)){
                    title.setCellValue(colunmName);
                    CellStyle errorStyle=createErrorStyle(workbook);
                    title.setCellStyle(errorStyle);
                    errorIndex=i;
                }else{
                    title.setCellValue(colunmName);
                }

            }

        }
        if (dataTable.Rows!=null&&dataTable.Rows.size()>0){
            for (int rindex=0;rindex<dataTable.Rows.size();rindex++) {
                Row erow= sheet.createRow(rindex+1);
                for (int i = 0; i <dataTable.Columns.size() ; i++) {
                    Cell data= erow.createCell(i, CellType.STRING);
                    if(i==errorIndex){
                        data.setCellValue(dataTable.getValue(rindex,errorIndex).toString());
                        CellStyle errorStyle=createErrorStyle(workbook);
                        data.setCellStyle(errorStyle);
                        errorIndex=i;
                    }else{
                        data.setCellValue(dataTable.getValue(rindex,i).toString());
                    }
                }
            }
        }
        return  workbook;
    }

    /**
     * 创建错误样式
     * @param workbook
     * @return
     */
    private static CellStyle createErrorStyle(Workbook workbook){
        CellStyle errStyle = workbook.createCellStyle();
        Font errorFont=workbook.createFont();
        errorFont.setColor(IndexedColors.RED.getIndex());
        errStyle.setFont(errorFont);
        return errStyle;
    }

    /**
     * 判断指定单元格是否合并了行
     * @param sheet 工作表
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet 工作表
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }


    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        Object value="";
        FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        try {
            if (cell!=null) {
                switch (cellValue.getCellTypeEnum()) {
                    case BOOLEAN:
                        value=cellValue.getBooleanValue();
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy年MM月dd日");
                            String readDateValue=dateFormat.format(cell.getDateCellValue());
                            return readDateValue;
                        } else {
                            String cnumber= NumberToTextConverter.toText(cell.getNumericCellValue());
                            cnumber= new BigDecimal(cnumber).toPlainString();
                            if (cnumber.indexOf(".")>0) {
                                BigDecimal b = new BigDecimal(cnumber);
                                double f1 = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                                value = String.valueOf(f1);
                            }else{
                                value=cnumber;
                            }
                            if(((String) value).length()>0&&((String) value).startsWith(".")){
                                value=((String) value).replace(".","0.");
                            }
                        }
                        break;
                    case STRING:
                        value=cellValue.getStringValue();
                        break;
                    case BLANK:
                        break;
                    case ERROR:
                        value=cellValue.getErrorValue();
                        break;
                    case FORMULA:
                        DecimalFormat df=new DecimalFormat("#.##");
                        String cnumber = df.format(cellValue.getNumberValue());
                        if (cnumber.indexOf(".")>0) {
                            BigDecimal b = new BigDecimal(cnumber);
                            double f1 = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                            value = String.valueOf(f1);
                        }else{
                            value=cnumber;
                        }
                        break;
                }
            }
        } catch (RuntimeException e) {
            try {
                value=cell.getRichStringCellValue();
            } catch (Exception e2) {
                value=cell.getNumericCellValue();
            }
        }
        return value.toString();

        /*if (cell == null){
            return "";
        }
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            if (cell.getStringCellValue()!=null&&cell.getStringCellValue().length()>0){
                return cell.getStringCellValue().trim();
            }
        } else if (cell.getCellTypeEnum().equals(CellType.BOOLEAN)) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellTypeEnum().equals(CellType.FORMULA)) {
            return cell.getCellFormula();
        } else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy年MM月dd日");
                String readDateValue=dateFormat.format(cell.getDateCellValue());
                return readDateValue;
            }else{
               // CellStyle cellStyle= cell.getCellStyle();
               // HSSFDataFormat dataFormat=new HSSFDataFormat().getFormat();
                return String.valueOf(cell.getNumericCellValue());
            }
        }
        return "";*/
    }


    /**
     * 判断Cell(单元格)是否为空
     * @param cell
     * @return
     */
    public static boolean isEmptyCell(Cell cell) {
        String cellContent = getCellValue(cell);
        if(StringTools.notEmpty(cellContent)){
            return false;
        } else{
            return true;
        }
    }


    /**
     * 关闭WorkBook
     * @author cyx
     * @date 2018/11/29 16:07
     * @param workbook
     * @return void
     */
    public static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
                //System.out.println("关闭workbook失败");
                LOG.error("关闭workbook失败", e);
            }
        }
    }

}
